package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import util.GetTime;

public class MemberCommunityDAO {
	
	/**
	 * 가입 커뮤니티 입력
	 * */
	public int insert(MemberCommunity mc){
		int result = 0;
		/*@@ String sql = "insert membercommunity (mid, cono, lvno, mcnick, mcregdate) " +
				"values (?,?,?,?,now())";*/
		String sql = "insert into membercommunity (mid, cono, lvno, mcnick, mcregdate) " +
		"values (?,?,?,?,?)";
		Connection conn = null;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
//			setting PrepareStatement Parameters
			pstmt.setString(1, mc.getMid());
			pstmt.setInt(2, mc.getCono());
			pstmt.setInt(3, mc.getLvno());
			pstmt.setString(4, mc.getMcnick());	
			pstmt.setTimestamp(5, GetTime.nowTime);
			
			result = pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}		
		return result;
	}
	
	/**
	 * 관리자 권한 부여
	 * -cono : 2,5,6,7,8 의 레코드를 정의한다.
	 * */
	public int insert(String mid , int lvno , String mcnick){
		int result = 0;
		
		String sql = "insert membercommunity (mid, cono, lvno, mcnick, mcregdate) " +
				"values (?,?,?,?,now())";
		
		Connection conn = null;
		
		try {
			
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			//setting PrepareStatement Parameters
			pstmt.setString(1, mid);	
			pstmt.setInt(2, 2);
			pstmt.setInt(3, lvno);
			pstmt.setString(4, mcnick);			
			result = pstmt.executeUpdate();
			
			//pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, mid);	
			pstmt.setInt(2, 5);
			pstmt.setInt(3, lvno);
			pstmt.setString(4, mcnick);			
			result = pstmt.executeUpdate();
			
			
			//pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, mid);	
			pstmt.setInt(2, 6);
			pstmt.setInt(3, lvno);
			pstmt.setString(4, mcnick);			
			result = pstmt.executeUpdate();
			
			//pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, mid);	
			pstmt.setInt(2, 7);
			pstmt.setInt(3, lvno);
			pstmt.setString(4, mcnick);			
			result = pstmt.executeUpdate();
			
			//pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, mid);	
			pstmt.setInt(2, 8);
			pstmt.setInt(3, lvno);
			pstmt.setString(4, mcnick);			
			result = pstmt.executeUpdate();
			
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}		
		return result;
	}
		
	
	
	/**
	 * 가입 커뮤니티 삭제(탈퇴)
	 * */
	public int delete(MemberCommunity mc){
		int result = 0;
		String sql = "delete from membercommunity where mid=? and cono=?";
		Connection conn = null;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
//				setting PrepareStatement Parameters
			pstmt.setString(1, mc.getMid());
			pstmt.setInt(2, mc.getCono());
			
			result = pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		return result;
	}
	
	
	/**
	 * 가입 커뮤니티 전 회원 정보 삭제
	 * */
	public int delete(int cono){
		int result = 0;
		String sql = "delete from membercommunity where cono=?";
		Connection conn = null;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setInt(1, cono);
			
			result = pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}
		return result;
	}
	
	
	/**
	 * 관리자용 해당 레코드를 삭제한다.
	 * */
	public int delete(String mid , int cono){
		int result = 0;
		String sql = "delete from membercommunity where mid=? and cono=?";
		Connection conn = null;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			//setting PrepareStatement Parameters
			pstmt.setString(1, mid);
			pstmt.setInt(2, cono);
			result = pstmt.executeUpdate();
			
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		} 
		finally 
		{
			try 
			{
				conn.close();
			} 
			catch (SQLException e) 
			{			
				e.printStackTrace();
			}
		}
		return result;
	}
	
	
	/**
	 * 가입 커뮤니티 레벨 수정
	 * */
	public int updateLevel(MemberCommunity mc){
		int result = 0;
		String sql = "update membercommunity set " +
				"lvno=? " +
				"where mid=? and cono=?";
		Connection conn = null;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
//				setting PrepareStatement Parameters
			pstmt.setInt(1, mc.getLvno());
			pstmt.setString(2, mc.getMid());
			pstmt.setInt(3, mc.getCono());
			
			result = pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}		
		return result;
	}
	
	/**
	 * 가입 커뮤니티 별명 수정
	 * */
	public int updateNick(MemberCommunity mc){
		int result = 0;
		String sql = "update membercommunity set " +
				"mcnick=? " +
				"where mid=? and cono=?";
		Connection conn = null;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
//			setting PrepareStatement Parameters
			pstmt.setString(1, mc.getMcnick());
			pstmt.setString(2, mc.getMid());
			pstmt.setInt(3, mc.getCono());
			
			result = pstmt.executeUpdate();
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {			
				e.printStackTrace();
			}
		}		
		return result;
	}
	
	
	public int countCommunityMembers(int cono){
		int result = 0;
		String sql = "select count(*) from membercommunity where cono=?";
		Connection conn = null;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, cono);
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			result = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {		
				e.printStackTrace();
			}
		}		
		return result;
	}

	public String getCommunityMcnick(int cono){
		String str = null;
		String sql = "select mcnick from membercommunity where cono=? and lvno=7";
		Connection conn = null;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, cono);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next())
				str = rs.getString(1);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {		
				e.printStackTrace();
			}
		}		
		return str;
	}
	
	public String getCommunityMid(int cono){
		String str = null;
		String sql = "select mid from membercommunity where cono=? and lvno=7";
		Connection conn = null;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, cono);
			ResultSet rs = pstmt.executeQuery();
			if(rs.next())
				str = rs.getString(1);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {		
				e.printStackTrace();
			}
		}		
		return str;
	}
	
	public int countPersonalCommunities(MemberCommunity mc){
		int result = 0;
		String sql = "select count(cono) from membercommunity where mid=?";
		Connection conn = null;
		try {
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, mc.getMid());
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			result = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {		
				e.printStackTrace();
			}
		}		
		return result;
	}
	
	public Vector<MemberCommunity> getMemberCommunity(String mid){
		Vector<MemberCommunity> vector = new Vector<MemberCommunity>();
		Connection conn = null;
		try{
			conn = ConnectionManager.getConnection();
			PreparedStatement pstmt = conn.prepareStatement("" +
					"select mc.*, coname from membercommunity mc, community co" +
					" where mc.cono=co.cono and mid=? and coopen in (2,3) and co.cono>9");
			
			pstmt.setString(1, mid);
			ResultSet rs = pstmt.executeQuery();

			MemberCommunity mc = new MemberCommunity();
			mc.setConame("가입한 커뮤니티");
			mc.setCono(0);
			vector.add(mc);
			while(rs.next()){
				mc = new MemberCommunity();
				mc.setMid(rs.getString(1));
				mc.setCono(rs.getInt(2));
				mc.setLvno(rs.getInt(3));
				mc.setMcnick(rs.getString(4));
				mc.setMcregdate(rs.getDate(5));
				mc.setConame(rs.getString(6));
				vector.add(mc);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {		
				e.printStackTrace();
			}
		}		
		
		return vector;
	}

	public MemberCommunity getMemberCommunity(MemberCommunity mc){

		Connection conn = null;
		try{
			String str = "";
			conn = ConnectionManager.getConnection();
			str="select * from membercommunity" +
					" where cono=? and mid=?";
				
			PreparedStatement pstmt = conn.prepareStatement(str);
			pstmt.setInt(1, mc.getCono());
			pstmt.setString(2, mc.getMid());
			
			ResultSet rs = pstmt.executeQuery();
	
			while(rs.next()){
				mc.setLvno(rs.getInt(3));
				mc.setMcnick(rs.getString(4));
				mc.setMcregdate(rs.getDate(5));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {		
				e.printStackTrace();
			}
		}		
		
		return mc;
	}
	
	/**
	 * 조성일 
	 * 필요한 함수 오버로딩
	 * 행당 커뮤니티의 전체 맴버 정보 취득
	 * */
	public Vector<MemberCommunity> getMemberCommunity(int cono){

		Connection conn = null;
		Vector<MemberCommunity> vector = new Vector<MemberCommunity>();
		MemberCommunity mc ;
		
		try{
			
			conn = ConnectionManager.getConnection();
			Statement stmt = conn.createStatement();
			
			String sql = "select mid,mcnick from membercommunity where cono="+cono;
		
			ResultSet rs = stmt.executeQuery(sql);
			
			while(rs.next()){
				
				mc = new MemberCommunity();
				mc.setMid(rs.getString(1));
				mc.setMcnick(rs.getString(2));
				//mc.setCono(rs.getInt(2));
				//mc.setLvno(rs.getInt(3));
				//mc.setMcregdate(rs.getDate(5));
				//mc.setConame(rs.getString(6));
				vector.add(mc);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {		
				e.printStackTrace();
			}
		}		
		
		return vector;
	}
	
	public Vector<MemberCommunity> getMemberCommunity(){
		Vector<MemberCommunity> vector = new Vector<MemberCommunity>();
		
		MemberCommunity mc = new MemberCommunity();

		mc.setConame("가입한 커뮤니티");
		mc.setCono(0);
		vector.add(mc);
		
		mc=new MemberCommunity();
		mc.setConame("로그인 해주세요");
		mc.setCono(0);
		vector.add(mc);
					
		return vector;
	}
	
	
	//커뮤니티 회원 가지고 오는 메소드
	public Vector<MemberCommunity> getMemberCommunityList(int cono,long currentPage, long rowsPerPage){
		
		long startindex = (currentPage - 1) * rowsPerPage + 1;
	    long endindex = currentPage * rowsPerPage;
	    int index = 0;
		Vector<MemberCommunity> vector = new Vector<MemberCommunity>();
		Connection conn = null;
		try{
			conn = ConnectionManager.getConnection();
			/*@@ PreparedStatement pstmt = conn.prepareStatement("" +
					"select mc.* from " +
					"(select meco.*,(@rownum:=@rownum+1) rownum from " +
					"(select membercommunity.*,(@rownum:=0) rnum  from " +
					"membercommunity where cono=?) meco) mc limit "+rowsPerPage*(currentPage-1)+","+rowsPerPage);*/
			
			PreparedStatement pstmt = conn.prepareStatement("select * from membercommunity where cono=?");
			
			pstmt.setInt(1, cono);
			ResultSet rs = pstmt.executeQuery();

			while(rs.next()){
				
				index += 1; 
			     
			     if(index >= startindex && index <= endindex){
				
				MemberCommunity mc = new MemberCommunity();
				mc.setMid(rs.getString(1));
				mc.setCono(rs.getInt(2));
				mc.setLvno(rs.getInt(3));
				mc.setMcnick(rs.getString(4));
				mc.setMcregdate(rs.getDate(5));
				//@@ mc.setConame(rs.getString(6));
				vector.add(mc);
			     }
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {		
				e.printStackTrace();
			}
		}		
		
		return vector;
	}
	
	public long getTotalRows(){
		long result = 0;
		Connection conn = ConnectionManager.getConnection();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select count(*) from membercommunity");
			rs.next();
			result = rs.getLong(1);
			rs.close();
			stmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(conn!=null)try{conn.close();}catch(Exception e){}
		}
		return result;
	}
	
	//커뮤니티 회원 수 가져요기
	public long getTotalRows(int cono){
		long result = 0;
		Connection conn = ConnectionManager.getConnection();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select count(*) from membercommunity where cono="+cono);
			rs.next();
			result = rs.getLong(1);
			rs.close();
			stmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(conn!=null)try{conn.close();}catch(Exception e){}
		}
		return result;
	}
	
	/**
	 * 커뮤니티별 별명 추출 메소드( mid, cono)
	 * */
	public static String getCommunityNick(String mid, long cono){
		String sql = "select mcnick from membercommunity where mid=? and cono=?";
		Connection conn = ConnectionManager.getConnection();
		String result = null;
		try{
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, mid);
			pstmt.setLong(2, cono);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				result = rs.getString(1);
			}
		} catch (Exception e){ e.printStackTrace(); 
		} finally { 
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
		}		
		return result!=null?result:mid;
	}
	
	/**
	 * 커뮤니티별 별명을 통한 아이디 추출 메소드( mid, cono)
	 * */
	public static String getCommunityMid(String mcnick, long cono){
		String sql = "select mid from membercommunity where mcnick=? and cono=?";
		Connection conn = ConnectionManager.getConnection();
		String result = null;
		try{
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, mcnick);
			pstmt.setLong(2, cono);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				result = rs.getString(1);
			}
		} catch (Exception e){ e.printStackTrace(); 
		} finally { 
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} 
		}		
		return result!=null?result:mcnick;
	}
	
	//닉네임 중복 여부 확인
	public int nickCheck(int cono, String nick){
		String sql = "select mid from membercommunity where mcnick=? and cono=?";
		Connection conn = ConnectionManager.getConnection();
		int result = 0;
		try{
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, nick);
			pstmt.setLong(2, cono);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				result++;
			}
		} catch (Exception e){ e.printStackTrace(); 
		} finally { 
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} 
		}		
		return result;		
	}
}
